package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.PsAnaDruginfoDao;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;

@Repository
public class PsAnaDruginfoDaoImpl implements PsAnaDruginfoDao {
	@Autowired
	private HibernateSqlUtil dao;
	
	/**
	 * @category 按地区统计(月)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsMonthAnaByArea(Pagination page) {
		String areaId = AreaUtil.getAreaStartCode(page.getConditions().get("area1"), page.getConditions().get("area2"), page.getConditions().get("area3"));
		String compCode = page.getConditions().get("compCode");
		String startMonth = page.getConditions().get("startMonth");
		String endMonth = page.getConditions().get("endMonth");
		StringBuffer sql = new StringBuffer()
		.append("	select area_id areaId,area_name areaName,del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo_area_mon  ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(areaId)){
			sql.append(" and area_id like :areaId ");
			params.put("areaId", areaId+"%");
		}
		if(StringUtils.isNotBlank(startMonth)){
			sql.append(" and date >= :startMonth ");
			params.put("startMonth", startMonth.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endMonth)){
			sql.append(" and date <= :endMonth ");
			params.put("endMonth", endMonth.replaceAll("-", ""));
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取上月配送率最高10个地区(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalLastMonthByArea(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select area_name areaName,del_ratio*100 delRatio from ana_compinfo_area_mon ")
		.append(" where del_comp_code = :compCode and date = :lastMonth order by del_ratio desc limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		params.put("lastMonth", ParseDate.parseDateToYm(ParseDate.addMonth(new Date(), -1)));
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
	
	/**
	 * @category 按地区统计(年)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsYearAnaByArea(Pagination page) {
		String areaId = AreaUtil.getAreaStartCode(page.getConditions().get("area1"), page.getConditions().get("area2"), page.getConditions().get("area3"));
		String compCode = page.getConditions().get("compCode");
		String startYear = page.getConditions().get("startYear");
		String endYear = page.getConditions().get("endYear");
		StringBuffer sql = new StringBuffer()
		.append("	select area_id areaId,area_name areaName,del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo_area_year  ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(areaId)){
			sql.append(" and area_id like :areaId ");
			params.put("areaId", areaId+"%");
		}
		if(StringUtils.isNotBlank(startYear)){
			sql.append(" and date >= :startYear ");
			params.put("startYear", startYear);
		}
		if(StringUtils.isNotBlank(endYear)){
			sql.append(" and date <= :endYear ");
			params.put("endYear", endYear);
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取去年配送率最高10个地区(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalLastYearByArea(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select area_name areaName,del_ratio*100 delRatio from ana_compinfo_area_year ")
		.append(" where del_comp_code = :compCode and date = :lastYear order by del_ratio desc limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		params.put("lastYear", Calendar.getInstance().get(Calendar.YEAR)-1);
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
	
	/**
	 * @category 按地区统计(日)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsAnaByArea(Pagination page) {
		String areaId = AreaUtil.getAreaStartCode(page.getConditions().get("area1"), page.getConditions().get("area2"), page.getConditions().get("area3"));
		String compCode = page.getConditions().get("compCode");
		String startDate = page.getConditions().get("startDate");
		String endDate = page.getConditions().get("endDate");
		StringBuffer sql = new StringBuffer()
		.append("	select area_id areaId,area_name areaName,del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo_area  ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(areaId)){
			sql.append(" and area_id like :areaId ");
			params.put("areaId", areaId+"%");
		}
		if(StringUtils.isNotBlank(startDate)){
			sql.append(" and date >= :startDate ");
			params.put("startDate", startDate.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" and date <= :endDate ");
			params.put("endDate", endDate.replaceAll("-", ""));
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取昨日配送率最高10个地区(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalYesterdayByArea(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select area_name areaName,del_ratio*100 delRatio from ana_compinfo_area ")
		.append(" where del_comp_code = :compCode and date = :yesterday order by del_ratio desc limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		params.put("yesterday", ParseDate.parseDateToYmd(ParseDate.addDate(new Date(), -1)));
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
	
	/**
	 * @category 按地区统计(季度)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsSeaAnaByArea(Pagination page) {
		String areaId = AreaUtil.getAreaStartCode(page.getConditions().get("area1"), page.getConditions().get("area2"), page.getConditions().get("area3"));
		String compCode = page.getConditions().get("compCode");
		String startSea = page.getConditions().get("startSea");
		String endSea = page.getConditions().get("endSea");
		StringBuffer sql = new StringBuffer()
		.append("	select area_id areaId,area_name areaName,del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo_area_sea  ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(areaId)){
			sql.append(" and area_id like :areaId ");
			params.put("areaId", areaId+"%");
		}
		if(StringUtils.isNotBlank(startSea)){
			sql.append(" and date >= :startSea ");
			params.put("startSea", startSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		if(StringUtils.isNotBlank(endSea)){
			sql.append(" and date <= :endSea ");
			params.put("endSea", endSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取上个季度配送率最高10个地区(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalLastSeaByArea(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select area_name areaName,del_ratio*100 delRatio from ana_compinfo_area_sea ")
		.append(" where del_comp_code = :compCode and date = :lastSea order by del_ratio desc limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		params.put("lastSea", ParseDate.getSea(ParseDate.addMonth(new Date(), -3)));
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
	
	/*-----------------------------------------------按时间查询----------------------------------------------------------*/
	/**
	 * @category 按时间统计(月)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsMonthAnaByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		String startMonth = page.getConditions().get("startMonth");
		String endMonth = page.getConditions().get("endMonth");
		StringBuffer sql = new StringBuffer()
		.append("	select del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo_mon  ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(startMonth)){
			sql.append(" and date >= :startMonth ");
			params.put("startMonth", startMonth.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endMonth)){
			sql.append(" and date <= :endMonth ");
			params.put("endMonth", endMonth.replaceAll("-", ""));
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取前十个月配送率(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalLastMonthByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select date,del_ratio*100 delRatio from ana_compinfo_mon ")
		.append(" where del_comp_code = :compCode  order by date  limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
	
	/**
	 * @category 按时间统计(年)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsYearAnaByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		String startYear = page.getConditions().get("startYear");
		String endYear = page.getConditions().get("endYear");
		StringBuffer sql = new StringBuffer()
		.append("	select del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo_year  ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(startYear)){
			sql.append(" and date >= :startYear ");
			params.put("startYear", startYear);
		}
		if(StringUtils.isNotBlank(endYear)){
			sql.append(" and date <= :endYear ");
			params.put("endYear", endYear);
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取前十年配送率(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalLastYearByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select date,del_ratio*100 delRatio from ana_compinfo_year ")
		.append(" where del_comp_code = :compCode  order by date  limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
	
	/**
	 * @category 按时间统计(日)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsAnaByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		String startDate = page.getConditions().get("startDate");
		String endDate = page.getConditions().get("endDate");
		StringBuffer sql = new StringBuffer()
		.append("	select del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(startDate)){
			sql.append(" and date >= :startDate ");
			params.put("startDate", startDate.replaceAll("-", ""));
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" and date <= :endDate ");
			params.put("endDate", endDate.replaceAll("-", ""));
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取前十天配送率(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalYesterdayByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select date,del_ratio*100 delRatio from ana_compinfo ")
		.append(" where del_comp_code = :compCode  order by date limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
	
	/**
	 * @category 按时间统计(季度)
	 * @date 2015年12月1日10:18:44
	 * @return
	 */
	@Override
	public Pagination getPsSeaAnaByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		String startSea = page.getConditions().get("startSea");
		String endSea = page.getConditions().get("endSea");
		StringBuffer sql = new StringBuffer()
		.append("	select del_ratio delRatio,date,total_amount totalAmount,del_amount delAmount,ret_amount retAmount,ret_ratio retRatio from ana_compinfo_sea  ")
		.append("	where del_comp_code = :compCode  ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		if(StringUtils.isNotBlank(startSea)){
			sql.append(" and date >= :startSea ");
			params.put("startSea", startSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		if(StringUtils.isNotBlank(endSea)){
			sql.append(" and date <= :endSea ");
			params.put("endSea", endSea.replaceAll("年第", "0").replaceAll("季度", ""));
		}
		sql.append(" order by  date desc");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取前十个季度配送率(柱状图)
	 * @date 2015-12-4
	 * @author wangbing
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getTotalLastSeaByTime(Pagination page) {
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql =new StringBuffer()
		.append("select date,del_ratio*100 delRatio from ana_compinfo_sea ")
		.append(" where del_comp_code = :compCode order by date  limit 10 ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compCode", compCode);
		return (List<Map<String, Object>>)dao.getSqlMapList(sql.toString(), params);
	}
}
